/**
 * 
 */
package se.ricardo.gamesorgs.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import se.ricardo.gamesorgs.api.common.ListTypes;

/**
 * Creates a connection to the database containing all the games
 * and systems data.<br>
 * This class also contains methods for retrieving specific data.
 * <br>
 * 
 * @author Ricardo Ocaña Leal
 *
 */
public class DBConnector {
	private Connection conn = null;
	
	public DBConnector() {
	}
	
	/**
	 * Creates a connection to the database.
	 * <br>
	 * 
	 * @throws
	 * SQLException if the database can't be accessed
	 * */
	public void createConnection() throws SQLException{
		String url = "jdbc:drizzle://localhost:3306/gamesdb";
		conn = DriverManager.getConnection(url, "game", "game");
	}
	
	/**
	 * Closes the connection to the database.
	 * <br>
	 * 
	 * @throws
	 * SQLException if the database can't be accessed
	 * */
	public void closeConnection() throws SQLException {
		if(conn != null) {
			conn.close();
		}
		
		System.out.println("56 DBConnector - Managed to close!");
	}
	
	/**
	 * Called to get all the stored company names.
	 * <br>
	 * 
	 * @return
	 * A {@link ResultSet} with all the data retrieved from
	 * the query.
	 * <br>
	 * 
	 * @throws
	 * SQLException
	 * */
	public ResultSet getAllCompanies() throws SQLException {
		return getQueryResults("select * from Companies " +
				"order by Name");
	}
	
	
	/**
	 * Called to get a detailed list of all the systems of a
	 * certain company, including the amount of stored games for
	 * each.<br>
	 * The integer listType decides which table in the database
	 * to retrieve the games from.
	 * <br>
	 * 
	 * @param
	 * company String with the name of the company.
	 * @param
	 * listType Integer representing which list to retrieve
	 * the games for.
	 * <br>
	 * 
	 * @return
	 * A {@link ResultSet} with all the data retrieved from
	 * the query.
	 * <br>
	 * 
	 * @throws
	 * SQLException
	 * */
	public ResultSet getCompanySystems(String company,
			int listType) throws SQLException {
		String query = "select Title, count(Gametitle) as Amount from ";
		
		switch(listType) {
			case ListTypes.COLLECTION: query += "CollectionGames "; break;
			case ListTypes.WISH: query += "WantedGames "; break;
			case ListTypes.SALE: query += "SaleGames ";
		}
		
		query += "right outer join " +
				"GenericSystems on System = Title " +
				"where CompanyName = '" + company + "' " +
				"group by Title";
		
		return getQueryResults(query);
	}
	
	/**
	 * Called to get all the systems by a certain company.
	 * <br>
	 * 
	 * @param
	 * company A string with the name of the company.<br>
	 * 
	 * @return
	 * A {@link ResultSet} with all the data retrieved from
	 * the query.<br>
	 * 
	 * @throws
	 * SQLException
	 * */
	public ResultSet getCompanySystems(String company) throws SQLException {
		return getQueryResults("select Title from GenericSystems " +
				"where CompanyName = '" + company + "' " +
				"order by Title;");
	}
	
	/**
	 * Called to get all the games from the personal collection, 
	 * that belong to a specific system.<br>
	 * The integer listType decides which table in the database
	 * to retrieve the games from.
	 * <br>
	 * 
	 * @param
	 * system A string with the name of the system.
	 * @param
	 * listType Integer representing which list to retrieve
	 * the games for.
	 * <br>
	 * 
	 * @return
	 * A {@link ResultSet} with all the data retrieved from
	 * the query.
	 * <br>
	 * 
	 * @throws
	 * SQLException
	 * */
	public ResultSet getPersonalGames(String system,
			int listType) throws SQLException {
		String query = "select * from ";
		
		switch(listType) {
			case ListTypes.COLLECTION: query += "CollectionGames "; break;
			case ListTypes.WISH: query += "WantedGames "; break;
			case ListTypes.SALE: query += "SaleGames ";
		}
		
		query += "where System = '" + system + "' " +
				"order by Gametitle;";
		
		return getQueryResults(query);
	}
	
	/**
	 * Adds a base game to the database.
	 * <br>
	 * 
	 * @param
	 * title String with the name of the game.
	 * @param
	 * system String with the name of the system.
	 * @param
	 * info String with the url to an infopage.
	 * <br>
	 * 
	 * @throws
	 * SQLException
	 * */
	public void addBaseGame(String title, String system,
			String info) throws SQLException {
		PreparedStatement st = conn.prepareStatement(
				"insert into StoredGames (Title, Systemtitle, InfoPage) " +
				"values (?, ?, ?);");
		st.setString(1, title);
		st.setString(2, system);
		st.setString(3, info);
		st.executeUpdate();
	}
	
	/**
	 * Verifies that the requested game combination exists in
	 * the database, as a "base" game.<br>
	 * Counts the amount of rows that exist in the table. The
	 * result should be "1".
	 * <br>
	 * 
	 * @param
	 * title String with the name of the game.
	 * @param
	 * system String with the system that the game belongs to.
	 * <br>
	 * 
	 * @return
	 * "True" if the game and system exist on the same row.<br>
	 * "False" otherwise.
	 * <br>
	 * 
	 * @throws
	 * SQLException
	 * */
	public boolean verifyBaseGame(String title, String system)
		throws SQLException {
		PreparedStatement st = conn.prepareStatement(
				"select count(*) as amount " +
				"from StoredGames where Title = (?) " +
				" and Systemtitle = (?);");
		st.setString(1, title);
		st.setString(2, system);
		ResultSet rs = st.executeQuery();
		
		int amount = 0;
		
		while(rs.next()) {
			amount = rs.getInt(1);
		}
		
		return amount == 1;
	}
	
	/**
	 * Called to add game to one of the personal lists.
	 * <br>
	 * 
	 * @param
	 * id The id of the game to remove.
	 * @param
	 * listType int representing the which list the game is included in.
	 * <br>
	 * 
	 * @throws
	 * SQLException
	 * */
	public void addPersonalGame(String title, String system,
			String cart, String instructions, String box,
			String comment, int listType) throws SQLException {
		switch(listType) {
			case ListTypes.COLLECTION:
				addCollectionGame(
						title, system, cart, instructions,
						box, comment);
				break;
			case ListTypes.WISH:
				addWishListGame(
						title, system, cart, instructions,
						box, comment);
				break;
			case ListTypes.SALE:
				addSaleListGame(
						title, system, cart, instructions,
						box, comment);
		}
	}
	
	/**
	 * Adds a game to the collection games table, in the database.
	 * The game must first have been added as a base game in StoredGames.
	 * <br>
	 * 
	 * @param
	 * title String with the name of the game.
	 * @param
	 * system String with the name of the system.
	 * @param
	 * cart String with a value from the condition scale, for the
	 * cartridge/disc.
	 * @param
	 * instructions String with a value from the condition scale, for the
	 * instructions.
	 * @param
	 * box String with a value from the condition scale, for the box.
	 * @param
	 * comment String with a user comment for the game.
	 * <br>
	 * 
	 * @throws
	 * SQLException
	 * */
	private void addCollectionGame(String title, String system,
			String cart, String instructions, String box,
			String comment) throws SQLException {
		PreparedStatement st = conn.prepareStatement("insert into " +
				"CollectionGames (Gametitle, System, Cartridge, " +
				"Instructions, Box, GameComment) values (" +
				"?, ?, ?, ?, ?, ?);");
		st.setString(1, title);
		st.setString(2, system);
		st.setString(3, cart);
		st.setString(4, instructions);
		st.setString(5, box);
		st.setString(6, comment);
		st.executeUpdate();
	}
	
	/**
	 * Adds a game to the wishlist games table, in the database.
	 * The game must first have been added as a base game in StoredGames.
	 * <br>
	 * 
	 * @param
	 * title String with the name of the game.
	 * @param
	 * system String with the name of the system.
	 * @param
	 * cart String with a value from the condition scale, for the
	 * cartridge/disc.
	 * @param
	 * instructions String with a value from the condition scale, for the
	 * instructions.
	 * @param
	 * box String with a value from the condition scale, for the box.
	 * @param
	 * comment String with a user comment for the game.
	 * <br>
	 * 
	 * @throws
	 * SQLException
	 * */
	private void addWishListGame(String title, String system,
			String cart, String instructions, String box,
			String comment) throws SQLException {
		PreparedStatement st = conn.prepareStatement("insert into " +
				"WantedGames (Gametitle, System, Cartridge, " +
				"Instructions, Box, GameComment) values (" +
				"?, ?, ?, ?, ?, ?);");
		st.setString(1, title);
		st.setString(2, system);
		st.setString(3, cart);
		st.setString(4, instructions);
		st.setString(5, box);
		st.setString(6, comment);
		st.executeUpdate();
	}
	
	/**
	 * Adds a game to the salelist games table, in the database.
	 * The game must first have been added as a base game in StoredGames.
	 * <br>
	 * 
	 * @param
	 * title String with the name of the game.
	 * @param
	 * system String with the name of the system.
	 * @param
	 * cart String with a value from the condition scale, for the
	 * cartridge/disc.
	 * @param
	 * instructions String with a value from the condition scale, for the
	 * instructions.
	 * @param
	 * box String with a value from the condition scale, for the box.
	 * @param
	 * comment String with a user comment for the game.
	 * <br>
	 * 
	 * @throws
	 * SQLException
	 * */
	private void addSaleListGame(String title, String system,
			String cart, String instructions, String box,
			String comment) throws SQLException {
		PreparedStatement st = conn.prepareStatement("insert into " +
				"SaleGames (Gametitle, System, Cartridge, " +
				"Instructions, Box, GameComment) values (" +
				"?, ?, ?, ?, ?, ?);");
		st.setString(1, title);
		st.setString(2, system);
		st.setString(3, cart);
		st.setString(4, instructions);
		st.setString(5, box);
		st.setString(6, comment);
		st.executeUpdate();
	}
	
	/**
	 * Called to delete a stored game from one of the personal lists.
	 * <br>
	 * 
	 * @param
	 * id The id of the game to remove.
	 * @param
	 * listType int representing the which list the game is included in.
	 * <br>
	 * 
	 * @throws
	 * SQLException
	 * */
	public void deletePersonalGame(int id, int listType) throws SQLException {
		switch(listType) {
			case ListTypes.COLLECTION:
				deleteCollectionGame(id); break;
			case ListTypes.WISH:
				deleteWishListGame(id); break;
			case ListTypes.SALE:
				deleteSaleListGame(id);
		}
	}
	
	/**
	 * Called to delete a game from the collection table, by
	 * specifying an id.
	 * <br>
	 * 
	 * @param
	 * id The id of the game to remove.
	 * <br>
	 * 
	 * @throws
	 * SQLException
	 * */
	private void deleteCollectionGame(int id) throws SQLException {
		PreparedStatement st = conn.prepareStatement("delete from " +
				"CollectionGames where Id = ?;");
		st.setInt(1, id);
		st.executeUpdate();
	}
	
	/**
	 * Called to delete a game from the wishlist table, by
	 * specifying an id.
	 * <br>
	 * 
	 * @param
	 * id The id of the game to remove.
	 * <br>
	 * 
	 * @throws
	 * SQLException
	 * */
	private void deleteWishListGame(int id) throws SQLException {
		PreparedStatement st = conn.prepareStatement("delete from " +
				"WantedGames where Id = ?;");
		st.setInt(1, id);
		st.executeUpdate();
	}
	
	/**
	 * Called to delete a game from the salelist table, by
	 * specifying an id.
	 * <br>
	 * 
	 * @param
	 * id The id of the game to remove.
	 * <br>
	 * 
	 * @throws
	 * SQLException
	 * */
	private void deleteSaleListGame(int id) throws SQLException {
		PreparedStatement st = conn.prepareStatement("delete from " +
				"SaleGames where Id = ?;");
		st.setInt(1, id);
		st.executeUpdate();
	}
	
	/**
	 * Called to edit an existing game from the database tables.
	 * <br>
	 * 
	 * @param
	 * id The id of the game.
	 * @param
	 * title The title of the game.
	 * @param
	 * cart String with a value from the condition scale, for the
	 * cartridge/disc.
	 * @param
	 * instructions String with a value from the condition scale, for the
	 * instructions.
	 * @param
	 * box String with a value from the condition scale, for the box.
	 * @param
	 * comment String with a user comment for the game.
	 * @param
	 * listType An int with the constant of the listtype that the game
	 * belongs in.
	 * <br>
	 * 
	 * @throws
	 * SQLException
	 * */
	public void editGame(int id, String title, String cart, String instructions,
			String box, String comment, int listType) throws SQLException {
		// TODO: Currently not able to edit a game to switch system.
		//Unless the game is edited from collection to wish, or other way around.
		StringBuilder stBuilder = new StringBuilder();
		
		switch(listType) {
			case ListTypes.COLLECTION: stBuilder.append("update CollectionGames "); break;
			case ListTypes.WISH: stBuilder.append("update WantedGames "); break;
			case ListTypes.SALE: stBuilder.append("update SaleGames ");
		}
		
		stBuilder.append("set Gametitle = ?, Cartridge = ?, Instructions = ?, " +
				"Box = ?, GameComment = ? where Id = ?;");
		
		PreparedStatement st = conn.prepareStatement(stBuilder.toString());
		
		st.setString(1, title);
		st.setString(2, cart);
		st.setString(3, instructions);
		st.setString(4, box);
		st.setString(5, comment);
		st.setInt(6, id);
		
		st.executeUpdate();
	}
	
	/**
	 * Executes a query and returns the results.
	 * <br>
	 * 
	 * @param
	 * query The string with the query.<br>
	 * 
	 * @return
	 * The result as a {@link ResultSet}.<br>
	 * 
	 * @throws
	 * SQLException
	 * */
	private ResultSet getQueryResults(String query) throws SQLException {
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(query);
		
		return rs;
	}
	
	/**
	 * Checks if the string is null.<br>
	 * Returns either null as a String or the contents
	 * surrounded by ''.
	 * <br>
	 * 
	 * @param
	 * s The String to check.
	 * <br>
	 * 
	 * @return
	 * String representation of null or the contents of
	 * "s" surrounded by ''.
	 * */
	private String getNullValue(String s) {
		return s == null ? "null" : "'"+s+"'";
	}
}
